package com.xl.core.util.poi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.xl.base.annotation.Excel;
import com.xl.core.util.Encodes;
import com.xl.core.util.StringUtil;

/**
 * Excel生成工具类
 * 
 * @author 梁华
 * 
 */
public final class ExcelUtil {

	/**
	 * 生成excel，通过HttpServletResponse输出流导出（单个sheet）
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	public static boolean exportNomalExcel(List<? extends Object> excelData,
			String sheetName, String excelName, HttpServletResponse response) {
		Map<String, List<? extends Object>> excelDataMap = new HashMap<String, List<? extends Object>>();
		List<String> sheetNameList = new ArrayList<String>();
		excelDataMap.put(sheetName, excelData);
		sheetNameList.add(sheetName);

		return exportNomalExcel(excelDataMap, sheetNameList, excelName,
				response);
	}

	/**
	 * 生成excel，通过HttpServletResponse输出流导出(多sheet)
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	public static boolean exportNomalExcel(
			Map<String, List<? extends Object>> excelData,
			List<String> sheetNameList, String excelName,
			HttpServletResponse response) {

		try {
			// excle写入文件
			OutputStream outputStream = response.getOutputStream();
			response.reset();
			// 处理中文文件名中文乱码问题
			response.setHeader("Content-Disposition", "attachment; filename="
					+ Encodes.urlEncode(excelName + ".xls"));
			response.setContentType("application/octet-stream; charset=utf-8");

			return createNomalExcelStream(excelData, sheetNameList,
					outputStream);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 生成excel文件(单个sheet)
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	public static boolean createNomalExcelFile(
			List<? extends Object> excelData, String sheetName,
			String excelFilePath) {
		Map<String, List<? extends Object>> excelDataMap = new HashMap<String, List<? extends Object>>();
		List<String> sheetNameList = new ArrayList<String>();
		excelDataMap.put(sheetName, excelData);
		sheetNameList.add(sheetName);
		return createNomalExcelFile(excelDataMap, sheetNameList, excelFilePath);
	}

	/**
	 * 生成excel文件(多sheet)
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	public static boolean createNomalExcelFile(
			Map<String, List<? extends Object>> excelData,
			List<String> sheetNameList, String excelFilePath) {

		FileOutputStream excelFile = null;
		try {
			// excle写入文件
			File file = new File(excelFilePath);
			if (!file.getParentFile().exists()) {
				file.getParentFile().mkdirs();
			}
			excelFile = new FileOutputStream(file);
			return createNomalExcelStream(excelData, sheetNameList, excelFile);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 生成excel，并直接写入到输出流中（单个sheet）
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	public static boolean createNomalExcelStream(
			List<? extends Object> excelData, String sheetName,
			OutputStream outputStream) {
		Map<String, List<? extends Object>> excelDataMap = new HashMap<String, List<? extends Object>>();
		List<String> sheetNameList = new ArrayList<String>();
		excelDataMap.put(sheetName, excelData);
		sheetNameList.add(sheetName);

		return createNomalExcelStream(excelDataMap, sheetNameList, outputStream);
	}

	/**
	 * 生成excel，并直接写入到输出流中（多sheet）
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	public static boolean createNomalExcelStream(
			Map<String, List<? extends Object>> excelData,
			List<String> sheetNameList, OutputStream outputStream) {

		HSSFWorkbook wb = null;
		try {
			// 生成excel
			wb = createExcel(excelData, sheetNameList);
			// excle写入文件
			wb.write(outputStream);

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (wb != null) {
					wb.close();
				}

				if (outputStream != null) {
					outputStream.flush();
					outputStream.close();
				}
			} catch (IOException e) {
			}
		}

		return true;
	}

	/**
	 * 生成excel文件
	 * 
	 * @param excelData
	 * @return
	 * @throws Exception
	 */
	private static HSSFWorkbook createExcel(
			Map<String, List<? extends Object>> excelData,
			List<String> sheetNameList) throws Exception {

		// 创建Excel工作簿对象
		HSSFWorkbook wb = new HSSFWorkbook();

		// 循环sheetNameList
		for (String sheetName : sheetNameList) {

			// 创建Excel工作表对象
			HSSFSheet sheet = wb.createSheet(sheetName);
			
			// 工作表数据
			List<? extends Object> sheetData = excelData.get(sheetName);
			if (sheetData == null || sheetData.size() == 0) {
				continue;
			}

			// 获取元素的数组
			Field[] fields = sheetData.get(0).getClass().getDeclaredFields();

			// 获取需要导出的元素数组
			List<Excel> excelField = new ArrayList<Excel>();
			Excel ex = null;
			for (Field field : fields) {
				ex = field.getAnnotation(Excel.class);
				if (ex != null) {
					excelField.add(ex);
				}
			}

			// 根据排序规则排序
			Collections.sort(excelField, new Comparator<Excel>() {

				@Override
				public int compare(Excel o1, Excel o2) {
					if (o1.sort() >= o2.sort()) {
						return 1;
					}
					if (o1.sort() < o2.sort()) {
						return -1;
					}
					return 0;
				}
			});

			// 创建Excel工作表的表头，设置列名等
			HSSFRow row = sheet.createRow(0);
			// 设置表头的格式，居中、加粗、16号字体
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
			style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);// 垂直居中
			HSSFFont font = wb.createFont();
			font.setFontHeightInPoints((short) 16);// 字号
			font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
			style.setFont(font);
			style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);// 背景色
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			// 循环设置表头，并获得每个单元格的格式
			HSSFCell cell = null;
			Excel excel = null;
			Map<String, HSSFCellStyle> cellStyleMap = new HashMap<String, HSSFCellStyle>();
			HSSFCellStyle cellStyle = null;
			HSSFFont cellFont = null;

			// 设置表头的序号
			cell = row.createCell(0);
			cell.setCellValue("序号"); // 列名
			cell.setCellStyle(style); // 格式
			sheet.autoSizeColumn(0, true);

			for (int i = 0; i < excelField.size(); i++) {

				excel = excelField.get(i);
				// 设置表头
				cell = row.createCell(i + 1);
				cell.setCellValue(excel.name()); // 列名
				cell.setCellStyle(style); // 格式

				// 获取单元格格式
				cellStyle = wb.createCellStyle();
				cellFont = wb.createFont();
				cellFont.setFontHeightInPoints((short) excel.size());// 字号
				if (excel.bold()) {
					cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
				}
				cellStyle.setFont(cellFont);
				if (excel.alignment()) {
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
				}
				if (excel.verticalAlignment()) {
					cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);// 垂直居中
				}

				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
				cellStyleMap.put(excel.cell(), cellStyle);

				// 设置列宽
				// sheet.setColumnWidth(i + 1,
				// excel.name().getBytes().length * 2 * 256);
				sheet.setColumnWidth(i + 1, excel.columnWidth() * 1536);
			}

			// 填充数据
			HSSFRow dataRow = null;
			Field field = null;
			for (int i = 0; i < sheetData.size(); i++) {

				Object data = sheetData.get(i); // 数据
				dataRow = sheet.createRow(i + 1); // 创建新数据行

				// 序号列
				cell = dataRow.createCell(0);
				cell.setCellStyle(style); // 格式
				cell.setCellValue(i + 1);

				// 填充每一单元格的数据
				for (int j = 0; j < excelField.size(); j++) {

					excel = excelField.get(j);
					// 获取列
					cell = dataRow.createCell(j + 1);
					cell.setCellStyle(cellStyleMap.get(excel.cell())); // 格式

					field = data.getClass().getDeclaredField(excel.cell());// 获取实体类的元素
					field.setAccessible(true); // 设置可操作，否则后续操作会出现权限问题
					Object fieldValue = field.get(data);// 获取元素的值
					if (fieldValue == null
							|| StringUtil.isEmpty(String.valueOf(fieldValue))) {
						cell.setCellValue("-");
						continue;
					}
					if (fieldValue instanceof Date) { // 日期类型
						cell.setCellValue((Date) fieldValue);
					} else {
						cell.setCellValue(String.valueOf(fieldValue));
					}
				}
			}

			// 冻结第一行
			sheet.createFreezePane(0, 1, 0, 1);
		}

		return wb;
	}

	public static void main(String[] args) throws Exception {

		TestExcel mem = new TestExcel();
		mem.setMemName("华仔");
		mem.setMemPwd("12eqwdeqwd3rcvaef4333打达到按时大是大非爱我的按时");
		mem.setMemQq("16455848defe");

		Map<String, List<? extends Object>> excelData = new HashMap<String, List<? extends Object>>();
		List<Object> data = new ArrayList<Object>();
		data.add(mem);
		excelData.put("人员", data);

		List<String> sheetList = new ArrayList<String>();
		sheetList.add("人员");

		ExcelUtil.createNomalExcelFile(excelData, sheetList,
				"E:\\excel\\test.xls");

	}
}

class TestExcel {

	@Excel(cell = "memName", name = "用户名")
	private String memName; // 用户名

	@Excel(cell = "memPwd", name = "登录密码")
	private String memPwd; // 登录密码

	@Excel(cell = "memQq", name = "QQ号")
	private String memQq; // QQ号

	public String getMemName() {
		return memName;
	}

	public void setMemName(String memName) {
		this.memName = memName;
	}

	public String getMemPwd() {
		return memPwd;
	}

	public void setMemPwd(String memPwd) {
		this.memPwd = memPwd;
	}

	public String getMemQq() {
		return memQq;
	}

	public void setMemQq(String memQq) {
		this.memQq = memQq;
	}

}
